﻿using System;
using System.Collections.Generic;
using System.Text;

namespace Fdb
{
    /// <summary>
    /// SqlServer操作实用类
    /// </summary>
    public class SqlServerUtil
    {
        /// <summary>
        /// 获取sql2000数据库表结构信息
        /// </summary>
        /// <returns></returns>
        public static string getDatabaseSQL_2000()
        {
            string databaseSQL_2000 = string.Empty;
            databaseSQL_2000 += "SELECT";
            databaseSQL_2000 += "   表名 = CASE WHEN a.colorder = 1 THEN d.name";
            databaseSQL_2000 += "             ELSE ''";
            databaseSQL_2000 += "        END ,";
            databaseSQL_2000 += "   表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')";
            databaseSQL_2000 += "              ELSE ''";
            databaseSQL_2000 += "         END ";
            databaseSQL_2000 += " FROM";
            databaseSQL_2000 += "   syscolumns a";
            databaseSQL_2000 += "   INNER JOIN sysobjects d ON a.id = d.id";
            databaseSQL_2000 += "                              AND d.xtype = 'U'";
            databaseSQL_2000 += "                              AND d.name <> 'sys.extended_properties'";
            databaseSQL_2000 += "                              AND d.name <> 'dtproperties'";
            databaseSQL_2000 += "   LEFT JOIN sysproperties f ON a.id = f.id";
            databaseSQL_2000 += "                                AND f.smallid = 0";
            databaseSQL_2000 += " WHERE";
            databaseSQL_2000 += "   ( CASE WHEN a.colorder = 1 THEN d.name";
            databaseSQL_2000 += "          ELSE ''";
            databaseSQL_2000 += "     END ) <> ''";
            databaseSQL_2000 += " ORDER BY";
            databaseSQL_2000 += "   表名";

            return databaseSQL_2000;
        }

        /// <summary>
        /// 获取sql2005数据库表结构信息
        /// </summary>
        /// <returns></returns>
        public static string getDatabaseSQL_2005()
        {
            string databaseSQL_2005 = string.Empty;
            databaseSQL_2005 += "SELECT \r\n";
            databaseSQL_2005 += "   表名 = CASE WHEN a.colorder = 1 THEN d.name \r\n";
            databaseSQL_2005 += "             ELSE '' \r\n";
            databaseSQL_2005 += "        END , \r\n";
            databaseSQL_2005 += "   表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') \r\n";
            databaseSQL_2005 += "              ELSE '' \r\n";
            databaseSQL_2005 += "         END \r\n";
            databaseSQL_2005 += "FROM \r\n";
            databaseSQL_2005 += "   syscolumns a \r\n";
            databaseSQL_2005 += "   INNER JOIN sysobjects d ON a.id = d.id \r\n";
            databaseSQL_2005 += "                              AND d.xtype = 'U' AND d.category=0 \r\n";
            databaseSQL_2005 += "                              AND d.name <> 'sys.extended_properties' \r\n";
            databaseSQL_2005 += "                              AND d.name <> 'dtproperties' \r\n";
            databaseSQL_2005 += "   LEFT JOIN sys.extended_properties f ON a.id = f.major_id \r\n";
            databaseSQL_2005 += "                                          AND f.minor_id = 0 \r\n";
            databaseSQL_2005 += "WHERE \r\n";
            databaseSQL_2005 += "   ( CASE WHEN a.colorder = 1 THEN d.name \r\n";
            databaseSQL_2005 += "          ELSE '' \r\n";
            databaseSQL_2005 += "     END ) <> '' \r\n";
            databaseSQL_2005 += "ORDER BY \r\n";
            databaseSQL_2005 += "   表名\r\n";

            return databaseSQL_2005;
        }

        /// <summary>
        /// 获取sql2000表字段信息
        /// </summary>
        /// <returns></returns>
        public static string getTableSQL_2000()
        {
            string tableSQL_2000 = string.Empty;
            tableSQL_2000 += "SELECT 表名=d.name, ";
            tableSQL_2000 += "表说明=isnull(f.value,''), ";
            tableSQL_2000 += "字段序号=a.colorder, ";
            tableSQL_2000 += "字段名=a.name, ";
            tableSQL_2000 += "标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ";
            tableSQL_2000 += "主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ";
            tableSQL_2000 += "SELECT name FROM sysindexes WHERE indid in( ";
            tableSQL_2000 += "SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  ";
            tableSQL_2000 += "))) then '√' else '' end, ";
            tableSQL_2000 += "类型=b.name, ";
            //tableSQL_2000 += "类型值=T.system_type_id, ";
            tableSQL_2000 += "占用字节数=a.length, ";
            tableSQL_2000 += "长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), ";
            tableSQL_2000 += "小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), ";
            tableSQL_2000 += "允许空=case when a.isnullable=1 then '√'else '' end, ";
            tableSQL_2000 += "默认值=isnull(e.text,''), ";
            tableSQL_2000 += "字段说明=isnull(g.[value],'') ";
            tableSQL_2000 += "FROM syscolumns a  ";
            tableSQL_2000 += "left join systypes b on a.xusertype=b.xusertype  ";
            tableSQL_2000 += "inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' ";
            tableSQL_2000 += "left join syscomments e on a.cdefault=e.id  ";
            tableSQL_2000 += "left join sysproperties g on a.id=g.id and a.colid=g.smallid  ";
            tableSQL_2000 += "left join sysproperties f on d.id=f.id and f.smallid=0  ";
            tableSQL_2000 += "order by a.id,a.colorder ";

            return tableSQL_2000;
        }

        /// <summary>
        /// 获取slq2005表字段信息
        /// </summary>
        /// <returns></returns>
        public static string getTableSQL_2005()
        {
            string tableSQL_2005 = string.Empty;

            tableSQL_2005 += "SELECT   \t\n";

            tableSQL_2005 += "表名=O.name,  \t\n";

            tableSQL_2005 += "表说明=PTB.[value],  \t\n";

            tableSQL_2005 += "Column_id=C.column_id,  \t\n";

            tableSQL_2005 += "字段名=C.name,  \t\n";

            tableSQL_2005 += "标识=ISNULL(IDX.PrimaryKey,N''),  \t\n";

            tableSQL_2005 += "[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,  \t\n";

            tableSQL_2005 += "Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,  \t\n";

            tableSQL_2005 += "类型=T.name,  \t\n";

            tableSQL_2005 += "类型值=T.system_type_id,  \t\n";

            tableSQL_2005 += "长度=C.max_length,  \t\n";

            tableSQL_2005 += "Precision=C.precision,  \t\n";

            tableSQL_2005 += "Scale=C.scale,  \t\n";

            tableSQL_2005 += "允许空=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,  \t\n";

            tableSQL_2005 += "[Default]=ISNULL(D.definition,N''),  \t\n";

            tableSQL_2005 += "字段说明=ISNULL(PFD.[value],N''),   \t\n";

            tableSQL_2005 += "IndexName=ISNULL(IDX.IndexName,N''),  \t\n";

            tableSQL_2005 += "IndexSort=ISNULL(IDX.Sort,N''),  \t\n";

            tableSQL_2005 += "Create_Date=O.Create_Date,  \t\n";

            tableSQL_2005 += "Modify_Date=O.Modify_date   \t\n";

            tableSQL_2005 += "FROM sys.columns C  \t\n";

            tableSQL_2005 += "INNER JOIN sys.objects O  \t\n";

            tableSQL_2005 += "ON C.[object_id]=O.[object_id]  \t\n";

            tableSQL_2005 += "AND O.type='U'  \t\n";

            tableSQL_2005 += "AND O.is_ms_shipped=0  \t\n";

            tableSQL_2005 += "INNER JOIN sys.types T  \t\n";

            tableSQL_2005 += "ON C.user_type_id=T.user_type_id  \t\n";

            tableSQL_2005 += "LEFT JOIN sys.default_constraints D  \t\n";

            tableSQL_2005 += "ON C.[object_id]=D.parent_object_id  \t\n";

            tableSQL_2005 += "AND C.column_id=D.parent_column_id  \t\n";

            tableSQL_2005 += "AND C.default_object_id=D.[object_id]  \t\n";

            tableSQL_2005 += "LEFT JOIN sys.extended_properties PFD  \t\n";

            tableSQL_2005 += "ON PFD.class=1   \t\n";

            tableSQL_2005 += "AND C.[object_id]=PFD.major_id   \t\n";

            tableSQL_2005 += "AND C.column_id=PFD.minor_id  \t\n";

            //tableSQL_2005+="-- AND PFD.name='Caption'  ";

            tableSQL_2005 += "LEFT JOIN sys.extended_properties PTB  \t\n";

            tableSQL_2005 += "ON PTB.class=1   \t\n";

            tableSQL_2005 += "AND PTB.minor_id=0   \t\n";

            tableSQL_2005 += "AND C.[object_id]=PTB.major_id  \t\n";

            tableSQL_2005 += "-- AND PFD.name='Caption'  \t\n";

            tableSQL_2005 += "LEFT JOIN  \t\n";

            tableSQL_2005 += "(  \t\n";

            tableSQL_2005 += "SELECT   \t\n";

            tableSQL_2005 += "IDXC.[object_id],  \t\n";

            tableSQL_2005 += "IDXC.column_id,  \t\n";

            tableSQL_2005 += "Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')  \t\n";

            tableSQL_2005 += "WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,  \t\n";

            tableSQL_2005 += "PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,  \t\n";

            tableSQL_2005 += "IndexName=IDX.Name  \t\n";

            tableSQL_2005 += "FROM sys.indexes IDX  \t\n";

            tableSQL_2005 += "INNER JOIN sys.index_columns IDXC  \t\n";

            tableSQL_2005 += "ON IDX.[object_id]=IDXC.[object_id]  \t\n";

            tableSQL_2005 += "AND IDX.index_id=IDXC.index_id  \t\n";

            tableSQL_2005 += "LEFT JOIN sys.key_constraints KC  \t\n";

            tableSQL_2005 += "ON IDX.[object_id]=KC.[parent_object_id]  \t\n";

            tableSQL_2005 += "AND IDX.index_id=KC.unique_index_id  \t\n";

            tableSQL_2005 += "INNER JOIN  \t\n";

            tableSQL_2005 += "(  \t\n";

            tableSQL_2005 += "SELECT [object_id], Column_id, index_id=MIN(index_id)  \t\n";

            tableSQL_2005 += "FROM sys.index_columns  \t\n";

            tableSQL_2005 += "GROUP BY [object_id], Column_id  \t\n";

            tableSQL_2005 += ") IDXCUQ  \t\n";

            tableSQL_2005 += "ON IDXC.[object_id]=IDXCUQ.[object_id]  \t\n";

            tableSQL_2005 += "AND IDXC.Column_id=IDXCUQ.Column_id  \t\n";

            tableSQL_2005 += "AND IDXC.index_id=IDXCUQ.index_id  \t\n";

            tableSQL_2005 += ") IDX  \t\n";

            tableSQL_2005 += "ON C.[object_id]=IDX.[object_id]  \t\n";

            tableSQL_2005 += "AND C.column_id=IDX.column_id   \t\n";

            //tableSQL_2005 += "--WHERE O.name=N'abc'  ";

            return tableSQL_2005;
        }
    }
}
